package cn.remex.db.sql;

import cn.remex.core.exception.ServiceCode;
import cn.remex.core.reflect.ReflectUtil;
import cn.remex.core.util.Assert;
import cn.remex.core.util.Judgment;
import cn.remex.core.util.Param;
import cn.remex.db.DbCvo;
import cn.remex.db.exception.RsqlException;
import cn.remex.db.lambdaapi.ColumnPredicate;
import cn.remex.db.lambdaapi.ListColumnPredicate;
import cn.remex.db.lambdaapi.ModelColumnPredicate;
import cn.remex.db.rsql.connection.RDBManager;
import cn.remex.db.rsql.connection.dialect.Dialect;
import cn.remex.db.rsql.model.Modelable;
import cn.remex.db.rsql.model.ModelableImpl;

import java.io.Serializable;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;
import java.util.function.Consumer;
import java.util.stream.Collectors;

import static cn.remex.db.sql.WhereRuleOper.*;

/**
 * @author Hengyang Liu yangyang8599@163.com
 * @since 2012-4-5
 */
public class Where<ParentType extends Modelable, T extends Modelable, NT extends Modelable> implements Serializable {
	private static final long serialVersionUID = -8929994826268738990L;
	private DbCvo<T, ParentType> superDbCvo;
	private Where<ParentType, T, NT> superWhere;

	private static void putNameParam(List<WhereRule> allRules, WhereRule rule, List<NamedParam> namedParams,Param<Integer> paramIndex,String nameParam) {
		namedParams.add(new NamedParam(paramIndex.param, nameParam, Types.CHAR, null));
		rule.setParamName(nameParam);
		allRules.add(rule);
	}
	/*
	 * @param tableName 这是表简名，不是beanName或者表真名。
	 */
	private static <T extends Modelable, ParentType extends Modelable> String ruleToSQL(String tableName, Param<Integer> tableIndex, WhereRule rule, List<NamedParam> namedParams, Param<Integer> paramIndex, List<WhereRule> allRules,
	                                                      DbCvo<T, ParentType> dbCvo) {
		Dialect dialect = RDBManager.getLocalSpaceConfig(dbCvo._getSpaceName()).getDialect();
		String sField = rule.getField(),
				rightPart = null,//参数名默认没有加编号，如果(!rule.isSubStatment()) && (ruleOper != isNull) && (ruleOper != notNull)需要加编号
				sOper = rule.getOp();
		if (sField.trim().length() == 0 || sOper.trim().length() == 0) {return "";} //忽略没有的rule
		WhereRuleOper ruleOper = WhereRuleOper.valueOf(sOper.trim());
		//String valueFullField=null;
		String percent = dialect.quoteAsString("%"),
				// 添加beanName避免数据库表的字段名冲突
				aliasName = tableName,
				fullField;

		//-----------------根据相关标识位&Data的类型来判断逻辑表达式（whereRule）可能的处理方式---------------------//
		//逻辑表达式的值目前支持四种：1.普通的值，将通过set*赋值给prepareStatementSql；2.SqlColumn代表某一列；3.子查询；4.可格式化的字符串
		//此代码块的目的：1.生成逻辑表达式右边的部分，2如果有参数需要生成nameParam命名参数
		if(rule.getData() instanceof SqlColumn){
			//第2种：如果值是一个列
			SqlColumn sqlColumn = (SqlColumn) rule.getData();
			rightPart = dialect.quoteKey(sqlColumn.getDbCvo()._getTableAliasName()) + "." + dialect.quoteKey(sqlColumn.getFieldAliasName());
		}else if(rule.getData() instanceof Expression){
			//第4种：如果是个表达式
			rightPart = ((Expression) rule.getData()).formatForWhereRule(rule.getField());
		}else if(rule.getData() instanceof SqlColumn){
			//第5种：如果是个列
			SqlColumn sqlColumn = (SqlColumn) rule.getData();
			rightPart = sqlColumn.getFieldAliasName();
		}else if(rule.getData() instanceof DbCvo){
			//第3种：如果是个子查询
			DbCvo subQuery = (DbCvo) rule.getData();
			subQuery._setParamIndex(paramIndex);
			subQuery._setTableAliasName("SS" + (tableIndex.param++));
			subQuery._setNamedParams(dbCvo._getNamedParams());
			subQuery._initForRsqlDao();
			subQuery.getParameters().forEach(dbCvo::$S);
			rightPart = "(" +subQuery._getPrettySqlString() + " " + Select.obtainSQLOrder(subQuery) +")";
		}else if(null != rule.getData() && (ruleOper== in || ruleOper== notIn)){
			StringBuilder ruleSubSelect = new StringBuilder(" ( ");
			//第1种：默认是一个普通数组
			if( Object[].class.isAssignableFrom(rule.getData().getClass()) ){
				for(Object arg:(Object[]) rule.getData())
					ruleSubSelect.append(dialect.quoteAsString(arg)).append(",");
				ruleSubSelect.deleteCharAt(ruleSubSelect.length()-1);
			}else {//2种就是一个值, 则转成字符串后用;split
				String inData = String.valueOf(rule.getData());
				for(String arg:inData.split(";"))
					ruleSubSelect.append(dialect.quoteAsString(arg)).append(",");
				ruleSubSelect.deleteCharAt(ruleSubSelect.length()-1);
			}
			rightPart = ruleSubSelect.append(" ) ").toString();
		}else{
			//第1种：默认是一个普通值
			if(ruleOper == isNull || ruleOper == notNull){
				rightPart = null;
			}else {
				paramIndex.param++;
				rightPart = ":" + rule.getField() + paramIndex.param;
				putNameParam(allRules, rule, namedParams, paramIndex, rule.getField() + paramIndex.param);
			}
		}

		//-----------------处理属性Model/List属性对应的aliasName（其表的虚拟名与基础表的不一致,需要去查找）---------------------//
		boolean isModelOrListField = sField.indexOf('.') > 0;
		if (isModelOrListField) { //只为model list 属性去寻找aliasName，基本字段的表名就是默认的T
			String parentField = sField.substring(0, sField.lastIndexOf('.'));
			Param<String> aliasNameParam = new Param<>(null);
			//插在该列是否被dbCvo使用并存在
			dbCvo._getRootColumn().anySubColumnMatch(
					p -> !Judgment.nullOrBlank(p.getFieldAliasName()) && parentField.equals(p.getFieldAliasName()),
					c -> aliasNameParam.param = c.getAliasName());
			Assert.notNullAndEmpty(aliasNameParam.param, ServiceCode.RSQL_SQL_ERROR, "约束的列不明确, 请通过extColumn(前端)或with*(后台)指定对应的列:" + parentField);
			aliasName = aliasNameParam.param;
		}

		//-----------------生成左右比较用的属性列---------------------//
		fullField = dialect.quoteKey(aliasName) + "." + dialect.quoteKey(isModelOrListField ? sField.substring(sField.lastIndexOf('.') + 1) : sField);


		StringBuilder cont = new StringBuilder();
		switch (ruleOper) {
			case equal: // 等于
				cont.append(fullField).append("= ").append(rightPart).append(" ");
				break;
			case notEqual: // 不等于
				cont.append(fullField).append(" !=  ").append(rightPart).append(" ");
				break;
			case lt: // 小于
				cont.append(fullField).append(" <  ").append(rightPart).append(" ");
				break;
			case le: // 小于等于
				cont.append(fullField).append(" <=  ").append(rightPart).append(" ");
				break;
			case gt: // 大于
				cont.append(fullField).append(" >  ").append(rightPart).append(" ");
				break;
			case ge: // 大于等于
				cont.append(fullField).append(" >=  ").append(rightPart).append(" ");
				break;
			case startsWith: // 以...开始
				cont.append(fullField).append(" LIKE  ").append(dialect.concat(rightPart, percent)).append(" ");
				break;
			case notStartsWith: // 不以...开始
				cont.append(fullField).append(" NOT LIKE  ").append(dialect.concat(rightPart, percent)).append(" ");
				break;
			case endsWith: // 以...结束
				cont.append(fullField).append(" LIKE ").append(dialect.concat(percent, rightPart)).append(" ");
				break;
			case notEndsWith: // 不以...结束
				cont.append(fullField).append(" NOT LIKE ").append(dialect.concat(percent, rightPart)).append(" ");
				break;
			case contains: // 包含
				cont.append(fullField).append(" LIKE ").append(dialect.concat(percent, rightPart, percent)).append(" ");
				break;
			case notContains: // 不包含
				cont.append(fullField).append(" NOT LIKE ").append(dialect.concat(percent, rightPart, percent)).append(" ");
				break;
			case _in: // 在....里面
				cont.append(" ").append(rightPart).append(" LIKE ").append(dialect.concat(percent, fullField, percent)).append(" ");
				break;
			case _ni: // 不在....里面
				cont.append(" ").append(rightPart).append(" NOT LIKE ").append(dialect.concat(percent, fullField, percent)).append(" ");
				break;
			case isNull: //值为空
				cont.append(fullField).append(" IS NULL ");
				break;
			case notNull: //值不为空
				cont.append(fullField).append(" IS NOT NULL ");
				break;

			case in: // 在....里面
				cont.append(fullField).append(" IN  ").append(rightPart).append(" ");
				break;
			case notIn: // 在....里面
				cont.append(fullField).append("NOT IN  ").append(rightPart).append(" ");
				break;
			case exist: // 存在子句结果为true
				cont.append(" EXISTS  ").append(rightPart).append(" ");
				break;
			case notExist: // 存在子句结果为true
				cont.append(" NOT EXISTS  ").append(rightPart).append(" ");
				break;
			default:
				// 默认是包含
				Assert.isTrue(false, ServiceCode.RSQL_SQL_ERROR, "Where 子句中比较参数设置错误");
				break;
		}
		return cont.toString();
	}

	private static void writeSQL(final StringBuilder result, final Where where, final String beanName,
	                             Param<Integer> tableIndex, final List<NamedParam> namedParams, Param<Integer> paramIndex, final List<WhereRule> allRules, final DbCvo dbCvo) {

		String groupOp = where.getGroupOp().toString();
		int rs = 0, gs = 0;
		boolean hasRules = where.rules != null && ((rs = where.rules.size()) > 0);
		boolean hasGroups = where.groups != null && ((gs = where.groups.size()) > 0);

		if (hasRules) {
			for (int j = 0, s = rs, gos = s - 1; j < s; j++) {
				WhereRule rule = (WhereRule) where.rules.get(j);
				result.append(ruleToSQL(beanName, tableIndex, rule, namedParams, paramIndex, allRules, dbCvo));
				if (j < gos) {//从倒数第二个开始不用添加AND 或者 OR连接符
					result.append(" ").append(groupOp).append(" ");
				}
			}
		}

		if (hasRules && hasGroups)
			result.append(" ").append(groupOp).append(" ");        //rules和groups之间的and 或者or连接符

		// 多组查询
		if (hasGroups) {
			for (int j = 0, s = gs, gos = s - 1; j < s; j++) {
				result.append(" (");
				Where group = (Where) where.groups.get(j);
				group.setSearch(where.search);
				writeSQL(result, group, beanName, tableIndex, namedParams, paramIndex, allRules, dbCvo);
				result.append(") ");
				if (j < gos) {//从倒数第二个开始不用添加AND 或者 OR连接符
					result.append(" ").append(groupOp).append(" ");
				}
			}
		}
	}

	private boolean search = false; // 是否是查询 true 或者 false

	private List<WhereRule> allRules = new ArrayList<>();
	private boolean filter = false;
	private WhereGroupOp groupOp = WhereGroupOp.AND; // 多字段查询时分组类型，主要是AND或者OR

	private List<Where<ParentType, T, NT>> groups = new ArrayList<>();// 多组组合
	private String nd; // 暂时不清楚啥用的
	private List<WhereRule> rules = new ArrayList<>(); // 多字段查询时候，查询条件的集合

	private String searchField; // 单字段查询的时候，查询字段名称

	private WhereRuleOper searchOper; // 单字段查询的时候，查询的操作

	private String searchString; // 单字段查询的时候，查询字段的值


	public Where<ParentType, T, NT> filterByGroup(Consumer<Where<ParentType, T, NT>> groupConsumer) {

		Where<ParentType, T, NT> group = new Where<>();
		group.setSuperDbCvo(this.superDbCvo);
		group.setSuperWhere(this);
		this.addGroup(group);

		groupConsumer.accept(group);

		return this;
	}

	/*
	 * 完成整个where 的构建,返回DbCvo
	 */
	public DbCvo<T, ParentType> end() {
		return this.superDbCvo;
	}

	/*
	 * 完成当前where的构建,返回上一级where
	 */
	public Where<ParentType, T, NT> endGroup() {
		return this.superWhere;
	}

	/*
	 * 一个可以AND 或者OR的规则
	 */
	public Where<ParentType, T, NT> filterBy(ColumnPredicate<NT> wp, WhereRuleOper oper, Object... args) {
//        wp.init(superDbCvo._obtainAOPBean());
//        String fieldName = superDbCvo.obtainPredicateBeanField(null);
//        this.addRule(fieldName, oper, value);
		ReflectUtil.eachFieldWhenGet(superDbCvo._obtainAOPBean(), b -> wp.init((NT) b), s -> this.addRule(s, oper, args));
		return this;
	}
	public <SSB extends ModelableImpl> Where<ParentType, T, NT> filterBy(ColumnPredicate<T> wp, WhereRuleOper oper, Class<SSB> subSelectBeanClass, Consumer<DbCvo<SSB, T>> subSelectSqlColumnConsumer) {
		DbCvo<SSB, T> subSelectDbCvo = new DbCvo<>(superDbCvo._getSpaceName(), subSelectBeanClass, true);
		subSelectSqlColumnConsumer.accept(subSelectDbCvo);
		ReflectUtil.eachFieldWhenGet(superDbCvo._obtainAOPBean(), b -> wp.init((T) b), fieldName -> this.addSubSelectRule(fieldName, oper, subSelectDbCvo));
		return this;
	}
	public <ST extends Modelable> Where<ParentType, T, NT> filterByModel(ModelColumnPredicate<T, T, ST> mcp, Consumer<SqlColumn<ParentType, T, T, ST>> sqlColumnConsumer){
		superDbCvo.withModel(mcp, sqlColumn->{
			//基于DbCvo的filter产生的控制条件将映射到SQL语句中最外层的WHERE子句中；
			//通过以下调换，实现SqlColumn自己的Filter和DbCvo的Filter在顶层Where子句和表连接On子句之间的切换
			sqlColumnConsumer.accept(sqlColumn);//sqlColumn操作的默认在WHere中的dbCvo中
		});
		return this;
	}
	public <ST extends Modelable> Where<ParentType, T, NT> filterByList(ListColumnPredicate<T, T, ST> mcp, Consumer<SqlColumn<ParentType, T, T, ST>> sqlColumnConsumer){
		superDbCvo.withList(mcp, sqlColumn->{
			//基于DbCvo的filter产生的控制条件将映射到SQL语句中最外层的WHERE子句中；
			//通过以下调换，实现SqlColumn自己的Filter和DbCvo的Filter在顶层Where子句和表连接On子句之间的切换
			sqlColumnConsumer.accept(sqlColumn);//sqlColumn操作的默认在WHere中的dbCvo中
		});
		return this;
	}

	/*
	 * 规则AND  OR
	 */
	public Where<ParentType, T, NT> filterOper(WhereGroupOp whereGroupOp) {
		this.setGroupOp(whereGroupOp);
		return this;
	}


	public void addGroup(Where where) {
		this.search = true;
		this.groups.add(where);
	}

	public void addRule(String field, WhereRuleOper ruleOper, Object value) {
		this.search = true;
		this.rules.add(new WhereRule(field, ruleOper, value));
	}

	public void addSubSelectRule(String field, WhereRuleOper ruleOper, DbCvo ruleDbCvo) {
		this.search = true;
		this.rules.add(new WhereRule(field, ruleOper, ruleDbCvo));
	}
//	public void addSubSelectRule(String field, WhereRuleOper ruleOper, Object... args) {
//		this.search = true;
//		this.rules.add(new WhereRule(field, ruleOper, args));
//	}

	public List<WhereRule> getAllRules() {
		return this.allRules;
	}

	public WhereGroupOp getGroupOp() {
		return this.groupOp;
	}

	public List<Where<ParentType, T, NT>> getGroups() {
		return this.groups;
	}

	public String getNd() {
		return this.nd;
	}

	public List<WhereRule> getRules() {
		return this.rules;
	}

	public String getSearchField() {
		return this.searchField;
	}

	public WhereRuleOper getSearchOper() {
		return this.searchOper;
	}

	public String getSearchString() {
		return this.searchString;
	}

	public boolean isSearch() {
		this.search = isFilter() || this.searchString != null;
		return this.search;
	}

	public boolean isFilter() {
		this.filter = (this.rules != null && this.rules.size() > 0) || (this.groups != null && this.groups.size() > 0);
		return this.filter;
	}

	public void setSearch(final boolean search) {
		this.search = search;
	}

//	public void setAllRules(final List<WhereRule> allRules) {
//		this.allRules = allRules;
//	}

	public void setFilter(final boolean filter) {
		this.filter = filter;
	}

	public void setGroupOp(final WhereGroupOp groupOp) {
		this.groupOp = groupOp;
	}

	public void setGroups(final List<Where<ParentType, T, NT>> groups) {
		this.groups = groups;
	}

	public void setNd(final String nd) {
		this.nd = nd;
	}

	public void setRules(final List<WhereRule> rules) {
		this.rules = rules;
	}

	public void setSearchField(final String searchField) {
		this.searchField = searchField;
	}

	public void setSearchOper(final WhereRuleOper searchOper) {
		this.searchOper = searchOper;
	}

	public void setSearchString(final String searchString) {
		this.searchString = searchString;
	}

	/*
	 * @param needWhere  是否需要where 关键字开头
	 * @param tableIndex 命名参数的序号
	 */
	public String toSQL(boolean needWhere, final String beanName, final List<NamedParam> namedParams, final Param<Integer> paramIndex, final Param<Integer> tableIndex, final DbCvo dbCvo) {
		this.optimize();
		StringBuilder result = new StringBuilder(needWhere ? " WHERE " : " ");
		if (!isSearch()) {
			return "";
		}
		if (isFilter()) {
			// 多字段的组合查询
			writeSQL(result, this, beanName, tableIndex, namedParams, paramIndex, this.allRules, dbCvo);

		} else if (getSearchField() != null && getSearchOper() != null && getSearchString() != null) {
			// 单字段组合
			throw new RsqlException(ServiceCode.RSQL_ERROR, "现在项目已经删除了对简单字段搜索的支持了！");
		} else {
			throw new RsqlException(ServiceCode.RSQL_ERROR, "设置了本次查询需要进行where搜索，但没有设置规则！");
		}

		return result.toString();
	}

	public void setSuperDbCvo(DbCvo<T, ParentType> superDbCvo) {
		this.superDbCvo = superDbCvo;
	}


	public Where<ParentType, T, NT> getSuperWhere() {
		return superWhere;
	}

	public void setSuperWhere(Where<ParentType, T, NT> superWhere) {
		this.superWhere = superWhere;
	}

	public DbCvo<T, ParentType> getSuperDbCvo() {
		return superDbCvo;
	}


	public Where<ParentType, T, NT> everyRule(Consumer<WhereRule> consumer) {
		this.rules.forEach(consumer::accept);
		this.groups.forEach(g -> g.everyRule(consumer::accept));
		return this;
	}

	public Where<ParentType, T, NT> optimize() {
		if(this.rules!=null)
			this.rules = this.rules.stream().filter(rule->
					!(contains.toString().equals(String.valueOf(rule.getOp())) && ("%".equals(rule.getData()) ||"null".equals(rule.getData()) || Judgment.nullOrBlank(rule.getData()) ))
// 如果eq开启忽略优化，择有巨大的漏洞
//					&&
//					!(eq.toString().equals(String.valueOf(rule.getOp())) && ("%".equals(rule.getData()) ||"null".equals(rule.getData()) || Judgment.nullOrBlank(rule.getData()) ))
			)

					.collect(Collectors.toList());
		if(this.groups!=null)
			this.groups = this.groups.stream().filter(group->{
				group.optimize();
				return (group.rules!=null && group.rules.size()>0 )||(group.groups!=null && group.groups.size()>0);
			}).collect(Collectors.toList());
		if(this.groups!=null && this.groups.size()==0)
			this.groups=null;
		if(this.rules!=null && this.rules.size()==0)
			this.rules=null;
		return this;
	}
}
